﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using BOM.entity;
using BOM.BaseData;

namespace BOM.dao
{
    class MaterialNew
    {
        //查询materialgroup的数据
        public static DataTable materialgroupChaXun()
        {
            string cmdText = "select * from materialgroup";
            try
            {
                DataTable table = DbService.getSQLTable(cmdText, "materialgroup");
                return table;
            }
            catch
            {
                return null;
            }
        }
        //判断当前节点是否有子节点
        public static DataTable BoollZiJieDian(int id)
        {
            string cmdText = "select * from materialgroup where fparentid = " + id + "";
            try
            {
                DataTable table = DbService.getSQLTable(cmdText, "materialgroup");
                return table;
            }
            catch
            {
                return null;
            }
        }
        //向表中新增数据
        public static bool XinZeng()
        {
            Material material = new Material();
            string Str = "'" + material.getFnumber() + "','" + material.getFname() + "','" + material.getFsize() + "',";
            Str += "'" + material.getFunit() + "','" + material.getFprice() + "'," + MaterialList.JieDianID + "," + material.getFpressarea() + "," + material.getFpressperimeter() + "";
            string cmdText = "insert into material (fnumber,fname,fsize,funit,fprice,fgroupid,fpressarea,fpressperimeter) values (" + Str + ")";
            try
            {
                int i = DbService.executeSQL (cmdText);
                if (i > 0)
                    return true;
                else
                    return false;
            }
            catch
            {
                return false;
            }
        }
        public static DataTable MaterialandMargroupIDCX(int fgroupid)
        {
            string cmdText = "select * from material where fgroupid = " + fgroupid + " order by fnumber";
            DataTable table = DbService.getSQLTable(cmdText, "material");
            if (table.Rows.Count >= 1)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //根据ID查看material表的数据
        public static DataTable materialIDCK(int fid)
        {
            string cmdText = "select * from material where fid = " + fid + "";
            DataTable table = DbService.getSQLTable(cmdText, "material");
            if (table.Rows.Count == 1)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //直接查询material表
        public static DataTable materialChaXun()
        {
            string cmdText = "select * from material";
            try
            {
                DataTable table = DbService.getSQLTable(cmdText, "material");
                return table;
            }
            catch
            {
                return null;
            }
        }
        //根据ID删除数据
        public static bool DeleteMaterial(int fid)
        {
            Materialgroup materialgroup = new Materialgroup();
            string cmdText = "delete from material where fid = " + fid + "";
            int values = DbService.executeSQL(cmdText);
            if (values == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //根据ID修改数据
        public static bool UpdateMaterial(int fid)
        { 
            Material material = new Material();
            string cmdText = "update material set fnumber = '" + material.getFnumber() + "',fname = '" + material.getFname() + "',fsize = '" + material.getFsize() + "',";
            cmdText += "funit = '" + material.getFunit() + "',fprice = '" + material.getFprice() + "',fpressarea = " + material.getFpressarea() + ",fpressperimeter = " + material.getFpressperimeter() + " where fid = " + fid + "";
            int values = DbService.executeSQL(cmdText);
            if (values == 1)
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        //根据 ID 查询material下级目录的值
        public static DataTable SubordinateMaterial(int fid)
        {
            string cmdText = "select * from material where fgroupid in (select fid from materialgroup where fparentid = " + fid + " or fid = " + fid + ")";
            DataTable table = DbService.getSQLTable(cmdText,"material");
            if (table.Rows.Count >= 1)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //根据材料分类和材料名称 查询是否为面积单位如果是返回规格 否则返回null
        public static string SelectMaterialgpSize(string materialname, string materialgroupname)
        {
            string cmdText = "select material.*,materialgroup.* from material,materialgroup where material.fgroupid = materialgroup.fid and material.fname='" + materialname + "' and materialgroup.fname='" + materialgroupname + "'";
            DataTable table = DbService.getSQLTable(cmdText,"materialandmaterial");
            if ((bool)table.Rows[0]["fpressarea"] == true)
            {
                return table.Rows[0]["fsize"].ToString();
            }
            else
            {
                return null;
            }
        }
        //根据材料分类和材料名称 查询是否为面积单位如果是返回规格 否则返回null
        public static string SelectMaterialgpPerimeter(string materialname, string materialgroupname)
        {
            string cmdText = "select material.*,materialgroup.* from material,materialgroup where material.fgroupid = materialgroup.fid and material.fname='" + materialname + "' and materialgroup.fname='" + materialgroupname + "'";
            DataTable table = DbService.getSQLTable(cmdText, "materialandmaterial");
            if ((bool)table.Rows[0]["fpressperimeter"] == true)
            {
                return table.Rows[0]["fsize"].ToString();
            }
            else
            {
                return null;
            }
        }


    }
}
